package com.ht.pms.core.dao.impl;

import com.ht.pms.core.dao.SalaryEmailDao;
import com.ht.pms.core.dto.dict.DictParamsDto;
import com.ht.pms.core.dto.send.*;
import com.ht.pms.core.dto.tempalte.Template;
import com.ht.pms.core.dto.upload.UpLoadTemp;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.stereotype.Service;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class SalaryEmailDaoImpl implements SalaryEmailDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<DictParamsDto> queryEamilTemplate(int templateType) {

        List<DictParamsDto> list = null;
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT t3.dict_name,t3.dict_column,t3.dict_table_name,t3.dict_code,t2.is_top");
        sb.append(" FROM t_template t1");
        sb.append(" INNER JOIN t_template_detail t2 ON t1.template_code = t2.template_code");
        sb.append(" INNER JOIN t_dict_params t3 ON t2.dict_code = t3.dict_code");
        sb.append(" WHERE t1.template_type = " + templateType + " and is_view=1  order by sort");
        try {
            list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper(DictParamsDto.class));
        } catch (EmptyResultDataAccessException e) {

        }
        return list;
    }

    @Override
    public Map<String, Object> createSendSalaryData(String sql) {
        Map<String, Object> map = null;
        try {
            map = jdbcTemplate.queryForMap(sql);
        } catch (EmptyResultDataAccessException e) {

        }
        return map;
    }

    @Override
    public List<SendSalaryTimeDto> querySalaryTime(String batchCode, int sendType) {
        List<SendSalaryTimeDto> list = null;
        StringBuilder sb = new StringBuilder();
        sb.append("select DISTINCT t1.time ,DATE_FORMAT(t1.check_end_time,'%c月%d日 %H:%i') as checkEndTime from t_upload_file t1 ");
        sb.append("WHERE 1=1 AND t1.state=1 AND t1.is_del=0");
        sb.append(" AND t1.batch_code='" + batchCode + "'");
        try {
            list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper(SendSalaryTimeDto.class));
        } catch (EmptyResultDataAccessException e) {

        }
        return list;
    }

    @Override
    public List<UpLoadTemp> queryUploadTemp(String batchCode) {
        List<UpLoadTemp> list = null;
        StringBuilder sb = new StringBuilder();
        sb.append("select t.time from t_upload_file t WHERE t.batch_code=");
        if (batchCode instanceof String) {
            sb.append("'" + batchCode + "'");
        }
        try {
            list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper(UpLoadTemp.class));
        } catch (EmptyResultDataAccessException e) {

        }
        return list;
    }

    @Override
    public void saveSendLog(NoticeEmailRecordDto noticeRecordSend) {
        String sql = "INSERT t_notice_email_record (batch_code,job_code,send_type,name,send_state,send_code,send_ym) VALUES(?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, noticeRecordSend.getBatchCode());
                ps.setString(2, noticeRecordSend.getJobCode());
                ps.setInt(3, noticeRecordSend.getSendType());
                ps.setString(4, noticeRecordSend.getName());
                ps.setInt(5, noticeRecordSend.getSendState());
                ps.setString(6, noticeRecordSend.getSendCode());
                ps.setString(7, noticeRecordSend.getSendYm());
            }
        });
    }


    @Override
    public List<SendBaseDto> findAllSalaryBase(String batchCode, String tableName) {
        List<SendBaseDto> list = null;
        String sql = "select t1.id,t2.job_number AS jobCode,t2.user_name AS name ,t2.email AS email from " + tableName + " t1 inner JOIN t_uc_user t2 ON t1.job_code=t2.job_number where 1=1 AND t1.audit_state=1 and t1.in_state=1 AND t1.send_state <> 1 and t1.batch_code='" + batchCode + "'";
        try {
            list = jdbcTemplate.query(sql, new BeanPropertyRowMapper(SendBaseDto.class));
        } catch (EmptyResultDataAccessException e) {

        }
        return list;
    }

    @Override
    public List<NoticeEmailRecordDto> findNoticRecordSend(String batchCode, String jobCode, int sendType) {
        List<NoticeEmailRecordDto> list = null;
        StringBuilder sb = new StringBuilder();
        // sendState 1 失败 0 成功
        sb.append("select t.batch_code,t.job_code,t.send_type,t.send_state,t.send_code from t_notice_email_record t where t.send_state=1 AND t.send_type=" + sendType);
        sb.append(" AND t.batch_code='" + batchCode + "'");
        sb.append(" AND t.job_code='" + jobCode + "'");
        try {
            list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper(NoticeEmailRecordDto.class));
        } catch (EmptyResultDataAccessException e) {
        }
        System.out.println(sb.toString());
        return list;
    }

    @Override
    public void updateNoticeRecordIsUsable(NoticeEmailRecordDto noticeRecordSend) {
        StringBuilder sb = new StringBuilder();
        sb.append("UPDATE t_notice_email_record SET send_state=0 WHERE ");
        sb.append(" batch_code= '" + noticeRecordSend.getBatchCode() + "'");
        sb.append(" AND job_code= '" + noticeRecordSend.getJobCode() + "'");
        sb.append(" AND send_type= " + noticeRecordSend.getSendType());

        jdbcTemplate.update(sb.toString());
    }

    @Override
    public List<NoticeSendDto> findNoticeSendList(String batchCode, int sendType) {
        List<NoticeSendDto> list = null;
        StringBuilder sb = new StringBuilder();
        sb.append("select t.batch_code,t.check_end_time,t.send_type from t_notice_send t WHERE 1=1 and state=0");
        sb.append(" AND t.batch_code='" + batchCode + "'");
        sb.append(" AND t.send_type=" + sendType);
        try {
            list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper(NoticeSendDto.class));
        } catch (EmptyResultDataAccessException e) {
        }
        System.out.println(sb.toString());
        return list;
    }

    @Override
    public void saveNoticeSendDto(NoticeSendDto noticeSendDto) {
        String sql = "INSERT into t_notice_send(batch_code,send_type,send_server)VALUES(?,?,?)";
        jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, noticeSendDto.getBatchCode());
                ps.setInt(2, noticeSendDto.getSendType());
                ps.setInt(3, noticeSendDto.getSendServer());
            }
        });
    }

    @Override
    public List<SalaryAccessDto> queryAccessSend(String jobCode, String batchCode, int sendType, String code,int type) {
        List<SalaryAccessDto> list = null;
        StringBuilder sb = new StringBuilder();
        sb.append("select t2.batch_code,t2.job_code from  ");
        if(type == 1){//邮件
            sb.append(" t_notice_email_record t2 ");
        }else { //钉钉
            sb.append(" t_notice_dingding_record t2 ");
        }

        sb.append(" WHERE 1=1  AND t2.send_state=0  AND t2.job_code='" + jobCode + "'");
        sb.append(" AND t2.batch_code='" + batchCode + "'");
        sb.append(" AND t2.send_type=" + sendType);
        sb.append(" AND t2.send_code='" + code + "'");
        try {
            list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper(SalaryAccessDto.class));
        } catch (EmptyResultDataAccessException e) {
        }
        System.out.println(sb.toString());
        return list;
    }

    @Override
    public List<SendBaseDto> findOneSalaryEmail(List<String> jobCodes, String batchCode, String tableName) {
        List<SendBaseDto> list = null;
        StringBuilder sbparam = new StringBuilder();
        for (String jobCode : jobCodes) {
            sbparam.append("'" + jobCode + "',");
        }
        String jobnumber = null;
        if (sbparam.toString().endsWith(",")) {
            jobnumber = sbparam.toString().substring(0, sbparam.toString().length() - 1);
        }
        StringBuilder sb = new StringBuilder();
        sb.append("select t1.id, t2.job_number AS jobCode,t2.user_name AS name ,t2.email AS email from " + tableName + " t1");
        sb.append(" inner JOIN t_uc_user t2 ON t1.job_code=t2.job_number where 1=1 and t1.batch_code='" + batchCode + "'");
        sb.append(" AND t1.audit_state=1 AND t1.in_state=1 and t2.job_number in(" + jobnumber + ")");
        try {
            list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper(SendBaseDto.class));
        } catch (EmptyResultDataAccessException e) {

        }
        System.out.println(sb.toString());
        return list;
    }

    @Override
    public void sendRecall(String batchCode, int sendType) {
        StringBuilder sb = new StringBuilder();
        sb.append("UPDATE t_notice_send SET state=1 WHERE ");
        sb.append(" batch_code= '" + batchCode + "'");
        sb.append(" AND send_type= " + sendType);

        jdbcTemplate.update(sb.toString());
        System.out.println(sb.toString());
    }

    @Override
    public void confirmSalaryData(int sendType, String batchCode, String jobCode,int type) {
        StringBuilder sb = new StringBuilder();
        sb.append("UPDATE " );
        if(type ==1 ){
            sb.append("  t_notice_email_record " );
        }else {
            sb.append("  t_notice_dingding_record " );
        }
        sb.append(" SET confirm_state=1 WHERE send_state=0");
        sb.append(" AND batch_code= '" + batchCode + "'");
        sb.append(" AND job_code= '" + jobCode + "'");
        sb.append(" AND send_type= " + sendType);

        jdbcTemplate.update(sb.toString());
        System.out.println(sb.toString());
    }

    @Override
    public void updateNoticeIsUsable(String batchCode, String jobCode, int sendType) {
        StringBuilder sb = new StringBuilder();
        sb.append("UPDATE t_notice_email_record SET send_state=2 WHERE send_state=0");
        sb.append(" AND batch_code= '" + batchCode + "'");
        sb.append(" AND job_code= '" + jobCode + "'");
        sb.append(" AND send_type= " + sendType);

        jdbcTemplate.update(sb.toString());
        System.out.println(sb.toString());
    }

    @Override
    public void updateNoticeIsUsableAll(String batchCode, int sendType) {
        StringBuilder sb = new StringBuilder();
        sb.append("UPDATE t_notice_email_record SET send_state=2 WHERE send_state=0");
        sb.append(" AND batch_code= '" + batchCode + "'");
        sb.append(" AND send_type= " + sendType);

        jdbcTemplate.update(sb.toString());
        System.out.println(sb.toString());
    }

    @Override
    public void updateRecordSendState(String tableName, String batchCode, String jobCode, int state) {
        StringBuilder sb = new StringBuilder();
        sb.append("UPDATE " + tableName + " SET send_state=" + state + " WHERE in_state=1 and audit_state=1 ");
        sb.append(" and batch_code= '" + batchCode + "'");
        if (!StringUtils.isBlank(jobCode)) {
            sb.append(" AND job_code= '" + jobCode + "'");
        }

        jdbcTemplate.update(sb.toString());
    }

    @Override
    public List<NoticeEmailRecordDto> findBatchCode(String batchCode) {
        List<NoticeEmailRecordDto> list = null;
        StringBuilder sb = new StringBuilder();
        sb.append("select DISTINCT t.batch_code ,s.template_type as sendType from t_upload_file t INNER JOIN t_template s ON t.template_code=s.template_code ");
        sb.append(" where 1=1");
        sb.append(" AND t.batch_code='" + batchCode + "'");
        try {
            list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper(NoticeEmailRecordDto.class));
        } catch (EmptyResultDataAccessException e) {
        }
        System.out.println(sb.toString());
        return list;


    }

    @Override
    public int countSendNumber(String batchCode, Integer state, String tableName) {
        int count = 0;
        StringBuilder sb = new StringBuilder();
        sb.append("select count(1) from " + tableName + " t where t.in_state=1 AND t.audit_state=1 ");
        sb.append(" AND t.batch_code='" + batchCode + "'");
        if (state != null) {
            sb.append(" AND t.send_state=" + state);
        }

        try {
            count = jdbcTemplate.queryForObject(sb.toString(), Integer.class);
        } catch (EmptyResultDataAccessException e) {
        }
        System.out.println(sb.toString());
        return count;
    }

    @Override
    public void updateRecordSendStateById(String tableName, Integer id, int state,String emailType) {
        StringBuilder sb = new StringBuilder();
        sb.append("UPDATE " + tableName + " SET send_state=" + state + ",email_type='"+emailType+"' WHERE in_state=1 and audit_state=1 ");
        sb.append(" and id= " + id);

        jdbcTemplate.update(sb.toString());
    }

    @Override
    public void updateEndTime(String batchCode,String endTime) {
        StringBuilder sb = new StringBuilder();
        sb.append("UPDATE t_upload_file  SET check_end_time='" + endTime + "' WHERE is_del=0 and state=1 ");
        sb.append(" and batch_code= '" + batchCode+"'");

        jdbcTemplate.update(sb.toString());
    }

    @Override
    public List<SendDataCountVo> selectSendData(String batchCode, String tableName) {
        List<SendDataCountVo> list = null;
        StringBuilder sb = new StringBuilder();
        sb.append("select t.job_code,t.send_state, t.dd_state from ");
        sb.append(tableName +" t ");
        sb.append(" where 1=1");
        sb.append(" AND t.audit_state=1 AND in_state=1 ");
        sb.append(" AND t.batch_code='" + batchCode + "'");
        try {
            list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper(SendDataCountVo.class));
        } catch (EmptyResultDataAccessException e) {
        }
        return list;
    }

    @Override
    public Map<String,Object> isFirstSendEmail(String jobCode,String batchCode ) {
        String sql = "SELECT l.email_type,l.create_time from t_dict_record_b l WHERE l.job_code='"+jobCode+"' and l.batch_code='"+batchCode+"' ORDER BY l.create_time DESC limit 1";
        Map<String,Object> result = jdbcTemplate.queryForMap(sql);
        return result;
    }
}
